(Music)
Hello and welcome. In this Video,
we'll go over SQL functions built into the database.
So let's get started.
While it is very much possible to first fetch data from a database and
then perform operations on it from your applications and notebooks,
most databases come with Built-in Functions.
These functions can be included in SQL statements, allowing you to perform operations on
data right within the database itself.
Using database functions can significantly reduce
the amount of data that needs to be retrieved from the database.
That is, reduces network traffic and use of bandwidth.
When working with large data sets, it may be faster to use built in functions,
rather than first retrieving the data into your application and then
executing functions on the retrieved data.
Note that it is also possible to create your own functions,
that is User-Defined Functions in the database; but that is a more advanced topic.
For the examples in this lesson, let's consider this PETRESCUE table in a database for a pet
rescue organization.
It records rescue transaction details and includes the columns: ID,
animal, quantity, cost, and rescue date.
For the purposes of this lesson, we have populated it with
several rows of data, as shown here.
What are aggregate or column functions?
An aggregate function takes a collection of like values,
such as all of the values in a column, as input, and returns a single value or null.
Examples of aggregate functions include: sum, minimum, maximum, average, etc.
Let's look at some examples based on the PETRESCUE table.
The sum function is used to add up all the values in a column.
To use the function, you write the column name within parenthesis,
after the function name.
For example, to add up all the values in the COST column,
select SUM (COST) from PETRESCUE.
When you use an aggregate function, the column in the result set
by default is given a number.
It is possible to explicitly name the resulting column.
For example, let's say we want to call the output column in
the previous query, as SUM_OF_COST.
select SUM(COST) as SUM_OF_COST from PETRESCUE.
Note the use of 'as' in this example.
Minimum, as the name implies, is used to get the lowest value.
Similarly, maximum is used to get the highest value.
For example, to get the maximum quantity of any animal rescue in
a single transaction, select MAX(QUANTITY) from PETRESCUE.
Aggregate functions can also be applied on a subset of data instead of an entire column.
For example, to get the minimum quantity of the ID column
for dogs.
select MIN(ID) from PETRESCUE where animal equals dog.
The average function is used to return the average or the mean value.
For example, to specify the average value of cost,
as: select AVG(COST) from PETRESCUE.
Note that we can perform mathematical operations between columns,
and then apply aggregate functions on them.
For example, to calculate the average cost per dog:
select AVG(COST divided by QUANTITY) from PETRESCUE where ANIMAL equals
Dog.
In this case, the cost is for multiple units; so we first divide
the cost by the quantity of the rescue.
Now let's look at the Scalar and String functions.
Scalar functions perform operations on individual values.
For example, to round up or down every value in the cost column
to the nearest integer, select ROUND (COST) from PETRESCUE.
There is a class of scalar functions called string functions,
that can be used for operations on strings.
That is char and varchar values.
For example, to retrieve the length of each value in animal column,
select LENGTH (ANIMAL) from PETRESCUE.
Uppercase and lowercase functions can be used to
return uppercase or lowercase values of strings.
For example, to retrieve animal values in uppercase:
select UPPERCASE (ANIMAL) from PETRESCUE.
Scalar functions can be used in the where clause.
For example, to get lowercase values of the animal column for cat,
select star from PETRESCUE where LOWERCASE(ANIMAL) equals cat.
This type of statement is useful for matching values in the where clause,
if you're not sure whether the values are stored in upper,
lower or mixed case in the table.
You can also have one function operate on the output of another function.
For example, to get unique cases for the animal column in
uppercase: select DISTINCT (UPPERCASE(ANIMAL)) from PETRESCUE.
In this video, we looked at some built-in SQL aggregate functions, such as
sum, minimum, maximum, and average We also looked at scalar and
string functions, such as round, lowercase, and uppercase.
Thank you for watching.
(Music)